﻿using DocumentFormat.OpenXml.Lite.Core.Office;
using DocumentFormat.OpenXml.Lite.Shared;
using DocumentFormat.OpenXml.Lite.SpreadsheetML.Excel.Base;
using DocumentFormat.OpenXml.Lite.SpreadsheetML.Excel.RunProperties;
using DocumentFormat.OpenXml.Lite.SpreadsheetML.Excel.Types;
using DocumentFormat.OpenXml.Lite.SpreadsheetML.Excel.WorksheetElements;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Samples.Excel
{
    class Formulas
    {
        public static void Create()
        {
            Workbook wb = DocumentManager.Instance.CreateSpreadsheet();

            Worksheet sheet1 = wb.CreateWorksheet("Sheet1", null);

            CreateHeaderCell(1, 1, sheet1, "Unit");
            CreateHeaderCell(1, 2, sheet1, "Price");
            CreateHeaderCell(1, 3, sheet1, "Discount");
            CreateHeaderCell(1, 4, sheet1, "Total");

            AddNumber(2, 1, sheet1, 5);
            AddNumber(2, 2, sheet1, 119.99);
            AddNumber(2, 3, sheet1, 5);

            AddNumber(3, 1, sheet1, 8);
            AddNumber(3, 2, sheet1, 68.99);
            AddNumber(3, 3, sheet1, 2);

            AddNumber(4, 1, sheet1, 30);
            AddNumber(4, 2, sheet1, 49.90);
            AddNumber(4, 3, sheet1, 7);
            
            //simple formula adding
            sheet1.CreateCell(2, 4).AddFormula("A2*B2*(100-C2)");

            //shared formulas
            Cell c1 = sheet1.CreateCell(3, 4);
            c1.Formula = new Formula()
            {
                FormulaType = ST_CellFormulaType.Shared,
                RangeOfCells = "D3:D4",
                SharedGroupIndex = 0,
                Value = "A3*B3*(100-C3)"
            };

            sheet1.CreateCell(4, 4).Formula = new Formula()
            {
                SharedGroupIndex = 0,
                FormulaType = ST_CellFormulaType.Shared
            };

            wb.Save(@"samples\formulas.xlsx");

            Workbook parsed = null;
            if (DocumentManager.Instance.OpenExcelDocument(@"samples\formulas.xlsx", out parsed))
            {
                Console.WriteLine("Sample created and parsed succesfully!");
            }
            Console.ReadLine();
        }

        private static void AddNumber(int row, int column, Worksheet sheet, double number)
        {
            Cell c = sheet.CreateCell(row, column);
            c.CellType = ST_CellType.Number;
            c.AddSimpleText(number.ToString().Replace(',', '.'));
        }

        private static void CreateHeaderCell(int row, int column, Worksheet sheet, string text)
        {
            Cell c = sheet.CreateCell(row, column);
            c.CellType = ST_CellType.InlineString;
            RichTextRun r = new RichTextRun()
            {
                Text = new SimpleText(text)
            };
            r.AddProperty(new Bold());
            c.InlineRichText = new InlineRichText();
            c.InlineRichText.Runs.Add(r);
        }
    }
}
